#!/bin/bash

#convert voters tsv to csv for mysql import
#get files from http://flvoters.com/downloads.html
#example wget "http://flvoters.com/download/20160229/CLL_20160307.txt"
county="CLL"

echo "Converting file to CSV format"
#add head line
echo '"id","lname","name suffix","fname","mname","no address","address","address2","city","state","zip","mail1","mail2","mail3","mailcity","mailstate","mailzip","mailcounty","gender","race","dob","rdate","party","precinct","Precinct Group","Precinct Split","Precinct Suffix","Voter Status","Congressional District","House District","Senate District","commission","School","area code","phone","email"' > data.csv

let lines=$(wc -l $county_*.txt |awk '{print $1}')

sed 's/   / /g' $county_*.txt|\
  sed 's/\t/","/g'|\
  sed "s/$county\",\"/\"/g"|\
  while read line;
  do 
    echo "$line\"" >> data.csv
  done 


echo "[[Compressing]]"
zip data.csv.zip data.csv

echo "To import:"
echo "==========="

cat << EOF
DROP TABLE voters;
CREATE TABLE voters(
   id                     INTEGER  NOT NULL PRIMARY KEY 
  ,lname                  TEXT NOT NULL
  ,name_suffix            TEXT
  ,fname                  TEXT NOT NULL
  ,mname                  TEXT NOT NULL
  ,no_address             TEXT NOT NULL
  ,address                TEXT NOT NULL
  ,address2               TEXT
  ,city                   TEXT NOT NULL
  ,state                  TEXT
  ,zip                    INTEGER  NOT NULL
  ,mail1                  TEXT
  ,mail2                  TEXT
  ,mail3                  TEXT
  ,mailcity               TEXT
  ,mailstate              TEXT
  ,mailzip                INTEGER 
  ,mailcounty             TEXT
  ,gender                 VARCHAR(1) NOT NULL
  ,race                   INTEGER  NOT NULL
  ,dob                    TEXT  NOT NULL
  ,rdate                  TEXT  NOT NULL
  ,party                  VARCHAR(3) NOT NULL
  ,precinct               INTEGER  NOT NULL
  ,Precinct_Group         BIT  NOT NULL
  ,Precinct_Split         NUMERIC(5,1) NOT NULL
  ,Precinct_Suffix        VARCHAR(1)
  ,Voter_Status           VARCHAR(3) NOT NULL
  ,Congressional_District INTEGER  NOT NULL
  ,House_District         INTEGER  NOT NULL
  ,Senate_District        INTEGER  NOT NULL
  ,commission             INTEGER  NOT NULL
  ,School                 INTEGER  NOT NULL
  ,area_code              INTEGER 
  ,phone                  INTEGER 
  ,email                  TEXT
  ,FIELD37                TEXT
);
EOF

echo "LOAD DATA INFILE '$PWD/data.csv' INTO TABLE voters FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES;"